package com.sinodata.bsm.cicp.collector.oracle;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.sinodata.bsm.cicp.collector.AbstractJDBCCollector;
import com.sinodata.bsm.cicp.exception.CollectorException;
import com.sinodata.bsm.common.constants.SeparatorConstants;
import com.sinodata.bsm.common.vo.ResPropertyValue;

public class SessionAndLockCollector extends AbstractJDBCCollector {
    private static Logger logger = Logger.getLogger(SessionAndLockCollector.class.getName());

    // private static final int DEADLOCK_WAIT_TIME = 60;

    /**
     * 采集Oracle会话列表、导致锁定的会话列表
     * 
     * @param pParmeters
     *            一个OracleServer的属性列表，仅包含ServerName
     * @return Oracle会话列表、导致锁定的会话列表
     * @throws CollectorException
     */

    @Override
    public List<ResPropertyValue> collect(String ip) throws CollectorException {
        // String sessionSql =
        // "select s.sid,s.status,serial#,username,osuser, machine,module,logon_time,l.CTIME lockwaittime "
        // + " from v$session s,v$lock l "
        // + " where s.type='USER' and s.lockwait=l.kaddr(+)";
        //
        // String sessionLockedSql = "Select Rpad(Oracle_Username, 10) o_Name,"
        // +
        // "Object_Name,Session_Id Sid,V$lock.Ctime,V$lock.Type, V$lock.Request,"
        // +
        // "Decode(Locked_Mode,0,'None',1,'Null',2,' 行级共享锁',3,' 行级排它锁',4,' 共享锁',5,' 共享行级排它锁',6,' 排它锁') Lock_Type "
        // + "From V$locked_Object, All_Objects, V$lock "
        // + "Where V$locked_Object.Object_Id = All_Objects.Object_Id"
        // + " And V$locked_Object.Object_Id = V$lock.Id1";

        List<ResPropertyValue> values = new ArrayList<ResPropertyValue>();
        String[] propMetas = getPropMeta();
        // String dbname = this.getParamMap().get("数据库名");
        // String dbtype = this.getParamMap().get("数据库类型");
        // String sql = this.getParamMap().get("会话列表命令");
        // String locksql = this.getParamMap().get("死锁会话列表命令");
        String dbname = this.getParamMap().get("DBName");
        String dbtype = this.getParamMap().get("DBType");
        String sql = this.getParamMap().get("SessionListCmd");
        String locksql = this.getParamMap().get("LockSessionListCmd");

        Connection conn = null;
        Statement statement = null;
        // ResultSet rst1 = null;

        String sessionCollectTextStr = SessionCollectTextStr(dbtype, sql, ip, dbname); // 获得会话列表

        String[] sessionLockedCollectTextStr = SessionLockedCollectTextStr(dbtype, locksql, ip, dbname);// 获得死锁会话列表
        long waitSessionLoginTimenumTotal = WaitSessionLoginTimeNum(dbtype, sql, ip, dbname);// 获得等待会话的总时间
        long waitSessionLoginTimenumAvg = 0L;

        try {
            int total = 0;
            int active = 0;
            int inActive = 0;
            // 获取Session 列表
            ResultSet rs = this.getCachedResultSet(dbtype, ip, dbname, sql);
            while (rs.next()) {
                total++;
                String status = rs.getString(2);
                if (status.equals("ACTIVE")) {
                    active++;
                } else if (status.equals("INACTIVE")) {
                    inActive++;
                }
                if (inActive > 0) {
                    waitSessionLoginTimenumAvg = waitSessionLoginTimenumTotal / inActive;
                }
            }
            ResPropertyValue propertyValueX = new ResPropertyValue();
            ResPropertyValue.PropValue[] propValues = new ResPropertyValue.PropValue[propMetas.length];
            propValues[0] = ResPropertyValue.createPropValue("" + total, propMetas[0]);
            propValues[1] = ResPropertyValue.createPropValue("" + active, propMetas[1]);
            propValues[2] = ResPropertyValue.createPropValue("" + sessionLockedCollectTextStr[0], propMetas[2]);
            propValues[3] = ResPropertyValue.createPropValue("" + sessionCollectTextStr, propMetas[3]);
            propValues[4] = ResPropertyValue.createPropValue("" + sessionLockedCollectTextStr[1], propMetas[4]);
            propValues[5] = ResPropertyValue.createPropValue("" + inActive, propMetas[5]);
            propValues[6] = ResPropertyValue.createPropValue("" + waitSessionLoginTimenumAvg, propMetas[6]);

            propertyValueX.setValues(propValues);
            values.add(propertyValueX);
            return values;
            // retVal.add(new String[] { "" + total, "" + active, "" +
            // lock_count,
            // sessionCollectTextStr, sessionLockedCollectTextStr });
            // return retVal;

        } catch (Exception ex) {
            logger.error("Qralce database " + dbname, ex);
            throw new CollectorException("Qralce database " + dbname, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
            }
        }
    }

    /**
     * 
     * @param ip
     *            String
     * @param serverName
     *            String
     * @return String
     */
    private String SessionCollectTextStr(String dbtype, String sql, String ip, String dbname) {
        StringBuilder sb = new StringBuilder("");
        //        sb.append("SID");// 编号
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("UserName");// 用户名
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Host Name");// 主机
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Host User");// 主机用户
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Login Time");// 登录时间
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Status"); // 状态
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append(SeparatorConstants.LIN_SEPARATOR);

        try {
            ResultSet rs = this.getCachedResultSet(dbtype, ip, dbname, sql);

            while (rs.next()) {
                String sid = rs.getString("sid");
                sb.append(sid == null ? "" : sid.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String userName = rs.getString("username");
                sb.append(userName == null ? "" : userName.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String machine = rs.getString("machine");
                sb.append(machine == null ? "" : machine.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String osUser = rs.getString("osuser");
                sb.append(osUser == null ? "" : osUser.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String logon_time = rs.getString("logon_time");
                if (logon_time.indexOf(".") > -1) {
                    sb.append(logon_time == null ? "" : logon_time.substring(0, logon_time.indexOf(".")).trim());
                } else {
                    sb.append(logon_time == null ? "" : logon_time.trim());
                }
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String status = rs.getString("status");
                sb.append(status == null ? "" : status.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                sb.append(SeparatorConstants.LIN_SEPARATOR);

            }
        } catch (Exception ex) {
            logger.error("while connect database : ", ex);
        }
        return sb.toString();
    }

    private String[] SessionLockedCollectTextStr(String dbtype, String sql, String ip, String dbname) {
        int lock_count = 0;
        StringBuffer sb = new StringBuffer();
        //        sb.append("UserName");// 用户名
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Locked Object");// 被锁对象
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("SID");
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Hold Lock Time");// 持有锁的时长
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Lock Protect Object Type");// 锁保护的对象类型
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Lock Model");// 锁模式
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append("Lock Type");// 锁类型
        //        sb.append(SeparatorConstants.COL_SEPARATOR);
        //        sb.append(SeparatorConstants.LIN_SEPARATOR);

        try {

            ResultSet rs = this.getCachedResultSet(dbtype, ip, dbname, sql);
            while (rs.next()) {
                String o_name = rs.getString("o_name");
                sb.append(o_name == null ? "" : o_name.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String object_name = rs.getString("object_name");
                sb.append(object_name == null ? "" : object_name.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String sid = rs.getString("sid");
                sb.append(sid == null ? "" : sid.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String ctime = rs.getString("ctime");
                sb.append(ctime == null ? "" : ctime.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String type = rs.getString("type");
                sb.append(type == null ? "" : type.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String request = rs.getString("request");
                sb.append(request == null ? "" : request.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                String locktype = rs.getString("lock_type");
                sb.append(locktype == null ? "" : locktype.trim());
                sb.append(SeparatorConstants.COL_SEPARATOR);
                sb.append(SeparatorConstants.LIN_SEPARATOR);

                lock_count++;
            }
        } catch (Exception ex) {
            logger.error("while connect database : ", ex);
        }
        return new String[] { lock_count + "", sb.toString() };
    }

    /**
     * 
     * @param ip
     *            String
     * @param serverName
     *            String
     * @return String
     */
    private Long WaitSessionLoginTimeNum(String dbtype, String sql, String ip, String dbname) {
        long waitSessionLoginTimenumTotal = 0L;
        long waitSessionStartTime = 0L;
        long diff = 0L;
        try {
            ResultSet rs = this.getCachedResultSet(dbtype, ip, dbname, sql);
            while (rs.next()) {
                if (rs.getString("status").equals("INACTIVE")) {
                    waitSessionStartTime = rs.getTimestamp("logon_time").getTime();//new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rs.getString("logon_time")).getTime();
                    // logger.debug("当前系统时间:" + System.currentTimeMillis() +
                    // "\n"
                    // + "服务启动时间:" + waitSessionStartTime + "\n");
                    diff = System.currentTimeMillis() - waitSessionStartTime;
                    // logger.debug("每次时间差:=============================>>" +
                    // diff + "\n\n");
                    waitSessionLoginTimenumTotal += diff;
                }
            }
            return waitSessionLoginTimenumTotal / 1000;
        } catch (Exception ex) {
            logger.error("while connect database : ", ex);
        }
        return 0L;
    }

    @Override
    public String[] getParamMeta() {
        return new String[] { "DBType", "DBName", "SessionListCmd", "LockSessionListCmd" };
    }

    @Override
    public String[] getPropMeta() {
        // return new String[] { "当前会话数", "活动会话数", "死锁会话数", "会话列表", "死锁会话列表",
        // "等待会话数", "平均等待时间" };
        return new String[] { "CurrentSessions", "ActiveSessions", "DeadlockSessions", "SessionsList", "DeadlockSessionsList", "WaitSessionNum", "AvgWaitTime" };
    }

}
